<?php
namespace app\index\controller;
use think\Controller;
use think\Db;
use think\Request;
use think\facade\Config;//引入配置
use TCPDF;
use Elasticsearch\ClientBuilder;

class Esproduct extends Base
{   

    private $client;
    private $redis;
    private $suoyin='ceshi';

    private $hosts = [
        'elastic:Lily8888@114.215.175.126:9200'  // SSL to IP + Port
    ];


    public function __construct(){
        parent::__construct();
        $this->client=ClientBuilder::create()->setHosts($this->hosts)->setRetries(0)->build();
    }

    public function index(){
        $sql="SELECT wname FROM lily_listkey_es4 GROUP BY wname";
        $wname=Db::query($sql);

        $params['index']='esproduct';
        $params['body']['settings']=[
            'number_of_shards' => 1,
            "number_of_replicas"=>0
        ];
        $data['class_id']=['type' => 'long'];
        $data['pid']=['type' => 'long'];
        $data['part']=['type' => 'keyword'];
        $data['part_small']=['type' => 'keyword'];
        foreach ($wname as $key => $value) {
            $data[$value['wname']]=['type'=>'keyword'];
            $data[md5($value['wname'])]=['type'=>'float'];
        }
        //dump($data);exit;
        $params['body']['mappings']['properties']=$data;
        $this->client->indices()->create($params);
    }


    public function index_upload(){
        $i=1;
        $sql="SELECT wname FROM lily_listkey_es4 GROUP BY wname";
        $wname=Db::query($sql);
        $field_arr=[];
        foreach ($wname as $key => $value) {
            if(!in_array($value['wname'],$field_arr)){
                $field_arr[]=$value['wname'];
            }
        }
        //$products = Db::table('lily_products')->field('id,class_id,part_number')->where('class_id','in','310,311,312,313,314,315,316,331,332,333,334,335,336,337,338,339,340,341,342,343,344,345,346,347,348,349,350,351,409,410,411,412,413,414,415,416,352,353,354,395,396,397,398,399,400,401,402,403,404,405,406,407,408,1176')->cursor();
        $products = Db::table('lily_products')->field('id,class_id,part_number')->cursor();
        foreach ($products as $key => $value) {
            $data=[];
            $params['body'][] = [
                'index' => [
                    '_index' => 'esproduct',
                    '_id' => $i,
                ]
            ];
            $data['class_id']=$value['class_id'];
            $data['pid']=$value['id'];
            $data['part']=$value['part_number'];
            $data['part']=strtolower($value['part_number']);
            $detail=Db::table('lily_products_detail')->field('key,value')->where([['is_fen_ge','=',0],['is_pi_pei','=',0],['is_show','=',1],['sup','=',null],['url','=',null],['is_show_key','=',1],['pid','=',$value['id']]])->select();

            $jin=['Thread Size','Drive Size'];
            $zhuan=['Outside Diameter Of Outer Ring','Inside Diameter Of Inner Ring','Height Of Overall Bearing Assembly'];

            foreach ($detail as $k => $v) {
                if(in_array($v['key'],$field_arr)){
                    $data[$v['key']]=$v['value'];

                    if(in_array($v['key'],$jin)){
                        if($v['key']=='Thread Size'){
                            if (strpos($v['value'], '"') !== false) {
                                $in=explode('-', $v['value']);
                                $end=str_replace("/","",str_replace(" ","",str_replace(".","",trim($in[1]))));
                                if (strpos($in[0], '/') !== false) {
                                    if (strpos(trim($in[0]), ' ') !== false) {
                                        $in2=explode(' ',str_replace('"',"",trim($in[0])));
                                        $in3=explode('/',trim($in2[1]));
                                        $qian=trim($in2[0])+trim($in3[0])/trim($in3[1]);
                                    }else{
                                        $in2=explode('/',str_replace('"',"",trim($in[0])));
                                        $qian=trim($in2[0])/trim($in2[1]);
                                    }
                                }else{
                                    $qian=trim(str_replace('"',"",$in[0]));
                                }

                                $q=500+$qian*100000;
                                
                                $data[md5($v['key'])]=$q;
                            }else{
                                if (strpos($v['value'], '-') !== false) {
                                    $in=explode('-', $v['value']);
                                    if($in[0]=='0'){
                                        $data[md5($v['key'])]='0.'.str_replace("-","",trim($v['value']));
                                    }else{
                                        $data[md5($v['key'])]=str_replace("-",".",trim($v['value']));
                                    }
                                }else{
                                    $data[md5($v['key'])]=str_replace("M","",trim($v['value']));
                                }
                            }
                        }
                        if($v['key']=='Drive Size'){
                            if (strpos($v['value'], '"') !== false) {
                                $in=explode('"', $v['value']);
                                if(is_numeric(trim($in[0]))){
                                    $data[md5($v['key'])]=trim($in[0]);
                                }else{
                                    if (strpos(trim($in[0]), '/') !== false) {  
                                        if (strpos(trim($in[0]), ' ') !== false) {
                                            $in2=explode(' ', trim($in[0]));
                                            if(is_numeric(trim($in2[0]))){
                                                $in3=explode('/', trim($in2[1]));
                                                if(is_numeric(trim($in3[0])) && is_numeric(trim($in3[1]))){
                                                    $data[md5($v['key'])]=trim($in2[0])+(trim($in3[0])/trim($in3[1]));
                                                }
                                            }
                                        }else{
                                            $in2=explode('/', trim($in[0]));
                                            if(is_numeric(trim($in2[0])) && is_numeric(trim($in2[1]))){
                                                $data[md5($v['key'])]=trim($in2[0])/trim($in2[1]);
                                            }
                                        }
                                    }
                                }
                            }else{
                                if(strpos($v['value'], 'mm') !== false) {
                                    $in=explode('mm', $v['value']);
                                    if(is_numeric(trim($in[0]))){
                                        $data[md5($v['key'])]=trim($in[0])+500;
                                    }
                                }else{
                                    $pv=preg_replace('/[^0-9]+/i','',$v['value']);
                                    $data[md5($v['key'])]=$pv+1000;
                                }
                            }
                        }
                    }else{
                        if(in_array($v['key'],$zhuan)){
                            $odi=array('1~15"','15~25"','25~30"','30~40"','40~48"','48~65"','65~100"','100"~greater');
                            $odm=array('1~18mm','18~30mm','30~270mm','270~400mm','400~490mm','490~530mm','530~635mm','635~695mm','695~745mm','745~835mm','835~890mm','890~950mm','950~1045mm','1045~1110mm','1110~1197mm','1197~1290mm','1290~1470mm','1470~1750mm','1750~2190mm','2190~2800mm','2800mm~greater');

                            $indi=array('1~7"','7~10"','10~15"','15~20"','20~25"','25~30"','30~35"','35~40"','40~45"','45~50"','50~70"','70~100"','100"~greater');
                            $indm=array('1~12mm','12~50mm','50~150mm','150~250mm','250~320mm','320~340mm','340~400mm','400~450mm','450~500mm','500~550mm','550~630mm','630~670mm','670~735mm','735~780mm','780~850mm','850~950mm','950~985mm','985~1050mm','1050~1200mm','1200~1400mm','1400~1700mm','1700~2000mm','2000~2500mm','2500mm~greater');

                            $hbi=array('0~1.5"','1.5~2.0"','2.0~2.2"','2.2~2.5"','2.5~3.2"','3.2~4.0"','4.0~5.5"','4.0~5.5"','5.5~8.0"','8.0"~greater');
                            $hbm=array('1~10mm','10~12.9mm','12.9~20mm','20~34mm','34~48mm','48~55mm','55~56mm','56~62mm','62~72mm','72~79mm','79~82mm','82~90mm','90~108mm','108~132mm','132~175mm','175mm~greater');

                            if($v['key']=='Outside Diameter Of Outer Ring'){
                                if (strpos($v['value'], '"') !== false) {
                                    foreach ($odi as $odk => $odv) {
                                        $odarr=explode('~', str_replace('"','',$odv));
                                        $bijia=trim(str_replace('mm','',str_replace('"','',$v['value'])));
                                        if(trim($odarr[1])=='greater'){
                                            if($bijia>$odarr[0]){
                                                $data[$v['key']]=$odv;
                                                $data[md5($v['key'])]=$odk;
                                            }
                                        }else{
                                            if($bijia>$odarr[0] && $bijia<=$odarr[1]){
                                                $data[$v['key']]=$odv;
                                                $data[md5($v['key'])]=$odk;
                                            }
                                        }
                                    }
                                }
                                if (strpos($v['value'], 'mm') !== false) {
                                    foreach ($odm as $odk => $odv) {
                                        $odarr=explode('~', str_replace('mm','',$odv));
                                        $bijia=trim(str_replace('mm','',str_replace('"','',$v['value'])));
                                        if(trim($odarr[1])=='greater'){
                                            if($bijia>$odarr[0]){
                                                $data[$v['key']]=$odv;
                                                $data[md5($v['key'])]=$odk;
                                            }
                                        }else{
                                            if($bijia>$odarr[0] && $bijia<=$odarr[1]){
                                                $data[$v['key']]=$odv;
                                                $data[md5($v['key'])]=$odk;
                                            }
                                        }
                                    }
                                }
                            }

                            if($v['key']=='Inside Diameter Of Inner Ring'){
                                if (strpos($v['value'], '"') !== false) {
                                    foreach ($indi as $odk => $odv) {
                                        $odarr=explode('~', str_replace('"','',$odv));
                                        $bijia=trim(str_replace('mm','',str_replace('"','',$v['value'])));
                                        if(trim($odarr[1])=='greater'){
                                            if($bijia>$odarr[0]){
                                                $data[$v['key']]=$odv;
                                                $data[md5($v['key'])]=$odk;
                                            }
                                        }else{
                                            if($bijia>$odarr[0] && $bijia<=$odarr[1]){
                                                $data[$v['key']]=$odv;
                                                $data[md5($v['key'])]=$odk;
                                            }
                                        }
                                    }
                                }
                                if (strpos($v['value'], 'mm') !== false) {
                                    foreach ($indm as $odk => $odv) {
                                        $odarr=explode('~', str_replace('mm','',$odv));
                                        $bijia=trim(str_replace('mm','',str_replace('"','',$v['value'])));
                                        if(trim($odarr[1])=='greater'){
                                            if($bijia>$odarr[0]){
                                                $data[$v['key']]=$odv;
                                                $data[md5($v['key'])]=$odk;
                                            }
                                        }else{
                                            if($bijia>$odarr[0] && $bijia<=$odarr[1]){
                                                $data[$v['key']]=$odv;
                                                $data[md5($v['key'])]=$odk;
                                            }
                                        }
                                    }
                                }
                            }

                            if($v['key']=='Height Of Overall Bearing Assembly'){
                                if (strpos($v['value'], '"') !== false) {
                                    foreach ($hbi as $odk => $odv) {
                                        $odarr=explode('~', str_replace('"','',$odv));
                                        $bijia=trim(str_replace('mm','',str_replace('"','',$v['value'])));
                                        if(trim($odarr[1])=='greater'){
                                            if($bijia>$odarr[0]){
                                                $data[$v['key']]=$odv;
                                                $data[md5($v['key'])]=$odk;
                                            }
                                        }else{
                                            if($bijia>$odarr[0] && $bijia<=$odarr[1]){
                                                $data[$v['key']]=$odv;
                                                $data[md5($v['key'])]=$odk;
                                            }
                                        }
                                    }
                                }
                                if (strpos($v['value'], 'mm') !== false) {
                                    foreach ($hbm as $odk => $odv) {
                                        $odarr=explode('~', str_replace('mm','',$odv));
                                        $bijia=trim(str_replace('mm','',str_replace('"','',$v['value'])));
                                        if(trim($odarr[1])=='greater'){
                                            if($bijia>$odarr[0]){
                                                $data[$v['key']]=$odv;
                                                $data[md5($v['key'])]=$odk;
                                            }
                                        }else{
                                            if($bijia>$odarr[0] && $bijia<=$odarr[1]){
                                                $data[$v['key']]=$odv;
                                                $data[md5($v['key'])]=$odk;
                                            }
                                        }
                                    }
                                }
                            }
                            
                        }else{
                            if (strpos($v['value'], '"') !== false) {
                                $in=explode('"', $v['value']);
                                if(is_numeric(trim($in[0]))){
                                    $data[md5($v['key'])]=trim($in[0]);
                                }else{
                                    if (strpos(trim($in[0]), '/') !== false) {  
                                        if (strpos(trim($in[0]), ' ') !== false) { 
                                            $in2=explode(' ', trim($in[0]));
                                            if(is_numeric(trim($in2[0]))){
                                                $in3=explode('/', trim($in2[1]));
                                                if(is_numeric(trim($in3[0])) && is_numeric(trim($in3[1]))){ 
                                                    $data[md5($v['key'])]=trim($in2[0])+(trim($in3[0])/trim($in3[1]));
                                                }else{
                                                    $data[md5($v['key'])]='10000';
                                                }
                                            }else{
                                                $data[md5($v['key'])]='10000';
                                            }
                                        }else{
                                            $in2=explode('/', trim($in[0]));
                                            if(is_numeric(trim($in2[0])) && is_numeric(trim($in2[1]))){ 
                                                $data[md5($v['key'])]=trim($in2[0])/trim($in2[1]);
                                            }else{
                                                $data[md5($v['key'])]='10000';
                                            }
                                        }
                                    }else{
                                        $data[md5($v['key'])]='10000';
                                    }
                                }
                            }else{
                                if(strpos($v['value'], 'mm') !== false) {
                                    $in=explode('mm', $v['value']);
                                    if(is_numeric(trim($in[0]))){
                                        $data[md5($v['key'])]=trim($in[0]);
                                    }else{
                                        $data[md5($v['key'])]='10000';
                                    }
                                }else{
                                    if(strpos($v['value'], 'psi') !== false && strpos($v['value'], ',') !== false) {
                                        
                                        $in=explode(',', $v['value']);
                                        $data[md5($v['key'])]=trim($in[0]);
                                    }else{
                                        if(is_numeric(trim($v['value']))){ 
                                            $data[md5($v['key'])]=trim($v['value']);  
                                        }else{
                                            if(trim($v['key'])=='System of Measurement'){
                                                if(trim($v['value'])=='Metric'){
                                                    $data[md5($v['key'])]='1';
                                                }else{
                                                    $data[md5($v['key'])]='0';
                                                }
                                            }else{
                                                if(strpos($v['value'], ' ') !== false) {
                                                    $in=explode(' ', $v['value']);
                                                    if(is_numeric(trim($in[0]))){
                                                        $data[md5($v['key'])]=trim($in[0]);
                                                    }else{
                                                        $data[md5($v['key'])]='10000';
                                                    }
                                                }else{
                                                    $data[md5($v['key'])]='10000';
                                                }
                                            }
                                        }
                                    }
                                }
                            } 
                        }
                    }
                }
            }
            $params['body'][] = $data;
            if (count($params['body']) % 1000 == 0) {
                $responses = $this->client->bulk($params);
                $params = ['body' => []];
                unset($responses);
            }
            $i++;
        }
        if (!empty($params['body'])) {
            $responses = $this->client->bulk($params);
        }  
    }

    public function search_data_api1(){
        $recommend = [
                'index' => 'esproduct2',
                'body' => [
                    "query"=>[
                        'term'=>[
                            'sear'.md5('Ball Dia')=>'1/2"'
                        ]
                ],
                    
            ]
        ];
        $data = $this->client->search($recommend);
        dump($data);
        return $data;
    }


    /*
      左侧筛选接口调用
      必要参数为class_id 数组形式 单个相等 多个 or
      筛选参数get(为去除杂质后的参数 如 gclid) 数组形式 若get参数为空 则不需要query查询
      字段参数：哪一个需要 被group的的字段名称
    */
    
    public function search_data_api($get,$class_id,$field,$is_in_m){
        if($is_in_m==1){
            $order=[
                ["count2" => "asc"],
                ["count" => "asc"],
                ["_term" => "asc"]
            ];
        }else{
           $order=[
                ["count" => "asc"],
                ["_term" => "asc"]
            ]; 
        }

        //dump($class_id);exit;
        if(empty($get)){
            $class_should=[];
            $j=0;
            foreach ($class_id as $key => $value) {
                $class_should[$j]['term']=['class_id'=>$value];
                $j++;
            }
            
            $recommend = [
                'index' => 'esproduct',
                'body' => [
                    "query"=>[
                        'bool'=>[
                            'should'=>$class_should
                        ]  
                    ],
                    "size"=>0,
                    "aggs"=>[
                        "popular_colors"=>[
                            "terms"=>[
                                "field"=>$field,
                                "size"=>10000,
                                "order"=>$order
                            ],
                            "aggs"=>[
                                "count2"=>[
                                  "min"=>["field"=>md5('System of Measurement')]
                                ],
                                "count"=>[
                                  "min"=>["field"=>md5($field)]
                                ],
                                
                            ]
                        ]
                    ]
                ]
            ];


        }else{
            $must=[];
            $i=0;
            $j=0;
            foreach ($get as $key => $value) {
                if($key=='part number'){
                    $must[$i]['wildcard']=['part'=>'*'.strtolower($value).'*'];
                }else{
                    $must[$i]['term']=[$key=>$value];
                }
                $i++;
            }
            $class_should=[];
            foreach ($class_id as $key => $value) {
                $class_should[$j]['term']=['class_id'=>$value];
                $j++;
            }
            $must[$i]['bool']['should']=$class_should;

            $recommend = [
                'index' => 'esproduct',
                'body' => [
                    "query"=>[
                        'bool'=>[
                            'must'=>$must
                        ]  
                    ],
                    "size"=>0,
                    "aggs"=>[
                        "popular_colors"=>[
                            "terms"=>[
                                "field"=>$field,
                                "size"=>10000,
                                "order"=>$order
                            ],
                            "aggs"=>[
                                "count2"=>[
                                  "min"=>["field"=>md5('System of Measurement')]
                                ],
                                "count"=>[
                                  "min"=>["field"=>md5($field)]
                                ],
                                
                            ]
                        ]
                    ]
                ]
            ];
        }
       //dump($recommend);exit;
//dump($this->client->search($recommend));
        $data = $this->client->search($recommend)['aggregations']['popular_colors']['buckets'];
        return $data;
    }


    public function search_data_count($get,$class_id){
        if(empty($get)){
            $recommend = [
                'index' => 'esproduct',
                'body' => [
                    "query"=>[
                        'term'=>[
                            'class_id'=>$class_id
                        ]  
                    ],
                    "aggs"=>[
                        "count"=>[
                            "cardinality"=>[
                                "field"=>'pid'  
                            ]  
                        ]
                    ]
                ]
            ];
        }else{
            $must=[];
            $i=0;
            foreach ($get as $key => $value) {
                if($key=='part number'){
                    $must[$i]['wildcard']=['part'=>'*'.$value.'*'];
                }else{
                    $must[$i]['term']=[$key=>$value];
                }
                $i++;
            }
            $must[$i]['term']=['class_id'=>$class_id];
            $recommend = [
                'index' => 'esproduct',
                'body' => [
                    "query"=>[
                        'bool'=>[
                            'must'=>$must
                        ]  
                    ],
                    "size"=>0,
                    "aggs"=>[
                        "count"=>[
                            "cardinality"=>[
                                "field"=>'pid'  
                            ]  
                        ]
                    ]
                ]
            ];
        }
        $data = $this->client->search($recommend)['aggregations']['count']['value'];
        return $data;
    }


}